import type { Router } from "express"
import type { Ioracle, IcallbackFn } from "../../types"

export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  router.post("/getReportData", (req, res) => {
    const { checkMonth, flowPhase,orgCode } = req.body
    let sql = " select a.org_code,a.check_month,c.chinese_name,a.flow_phase,a.header_id "
    sql +=
      " from xx_general_asset_report a,xx_general_asset_report_flow b,user_info_n c "
    sql += " where a.header_id = b.header_id "
    sql += " and b.action_klm = c.account "
    sql += " and a.current_seq = b.current_seq "
    sql += checkMonth && " and a.check_month = '" + checkMonth + "' "
    sql += orgCode && " and a.org_Code = '"+ orgCode +"' "
    sql +=
      flowPhase &&
      " and a.flow_phase = decode('" +
        flowPhase +
        "','Y','結案','N','開單','主管簽核') "
    callbackFn(res, oracle.connChaos, { sql })
  })

  //总盘点资料
  /*router.post("/getReportDetail", (req, res) => {
    const { checkMonth } = req.body
    let sql = " select asset_type,nvl(total_cnt,0) total_cnt "
    sql += " ,nvl(check_cnt,0) check_cnt "
    sql += " ,nvl(total_cnt,0) - nvl(check_cnt,0) leave_cnt "
    sql += " from "
    sql += " (select '已入籍' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql +=
      " from xx_general_asset_check_header a, xx_general_asset_check_line b "
    sql += " where a.header_id = b.header_id "
    sql += " and a.check_month = '" + checkMonth + "' "
    sql += " and asset_type = 'A' "
    sql += " union "
    sql += " select '待驗收' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql +=
      " from xx_general_asset_check_header a, xx_general_asset_check_line b "
    sql += " where a.header_id = b.header_id "
    sql += " and a.check_month = '" + checkMonth + "' "
    sql += " and asset_type = 'Exist' "
    sql += " union "
    sql += " select '租賃' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql +=
      " from xx_general_asset_check_header a, xx_general_asset_check_line b "
    sql += " where a.header_id = b.header_id "
    sql += " and a.check_month = '" + checkMonth + "' "
    sql += " and asset_type = 'Rent' "
    sql += " union "
    sql += " select '無償借用' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql +=
      " from xx_general_asset_check_header a, xx_general_asset_check_line b "
    sql += " where a.header_id = b.header_id "
    sql += " and a.check_month = '" + checkMonth + "' "
    sql += " and asset_type = 'Free' "
    sql += " union "
    sql += " select 'DEMO' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql +=
      " from xx_general_asset_check_header a, xx_general_asset_check_line b "
    sql += " where a.header_id = b.header_id "
    sql += " and a.check_month = '" + checkMonth + "' "
    sql += " and asset_type = 'Demo' "
    sql += " union "
    sql += " select '客供' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql +=
      " from xx_general_asset_check_header a, xx_general_asset_check_line b "
    sql += " where a.header_id = b.header_id "
    sql += " and a.check_month = '" + checkMonth + "' "
    sql += " and asset_type = 'Offer' "
    sql += " union "
    sql += " select '合計' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line) "
    sql +=
      " order by decode(asset_type,'已入籍',0,'待驗收',1,'租賃',2,'無償借用',3,'DEMO',4,'客供',5,'合計',6,7) "
    callbackFn(res, oracle.connChaos, { sql })
  })*/
  router.post("/getReportDetail", (req, res) => {
    const { checkMonth,orgCode } = req.body
    let sql = " select asset_type,nvl(total_cnt,0) total_cnt "
    sql += " ,nvl(check_cnt,0) check_cnt "
    sql += " ,nvl(total_cnt,0) - nvl(check_cnt,0) leave_cnt "
    sql += " ,nvl(admin_cnt,0) admin_cnt "
    sql += " from "
    sql += " (select 'A類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'A-%' "
    sql += " union "
    sql += " select 'B類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'B-%' "
    sql += " union "
    sql += " select 'C類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'C-%' "
    sql += " union "
    sql += " select 'D類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'D-%' "
    sql += " union "
    sql += " select 'E類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'E-%' "
    sql += " union "
    sql += " select 'F類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'F-%' "
    sql += " union "
    sql += " select 'G類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'G-%' "
    sql += " union "
    sql += " select 'H類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'H-%' "
    sql += " union "
    sql += " select 'Exist' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'Exist' "
    sql += " union "
    sql += " select 'Rent' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'Rent' "
    sql += " union "
    sql += " select 'Free' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'Free' "
    sql += " union "
    sql += " select 'Demo' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'Demo' "
    sql += " union "
    sql += " select 'Offer' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " ) "
    sql += " and asset_type = 'Offer' "
    sql += " union "
    sql += " select 'Total' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " ,sum(decode(admin_check,null,0,1)) admin_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ( "
    sql += " select header_id from xx_general_asset_check_header "
    sql += " where check_month = '" + checkMonth + "' "
    if(orgCode === "CC") {
      sql += " and (dept_code like 'CC%' or dept_code like 'CB%' or dept_code like 'CA%') "
    }else if(orgCode === "CF"){
      sql += " and (dept_code like 'CF%' or dept_code like 'FT%') "
    }else if(orgCode === "CH"){
      sql += " and (dept_code like 'CH%' or dept_code in ('FPCIO','SMTIO')) "
    }else{
      sql += " and dept_code like 'HB%' "
    }
    sql += " )) "
    sql += " order by decode (asset_type,'A類', 0,'B類', 1 "
    sql += " ,'C類', 2,'D類', 3,'E類', 4 "
    sql += " ,'F類',5,'G類',6,'H類',7 "
    sql += " ,'Exist', 8,'Rent', 9,'Free', 10,'Demo' "
    sql += " , 11,'Offer', 12,'Total', 13,14) "
    callbackFn(res, oracle.connChaos, { sql })
  })

  router.post("/signReportList", (req, res) => {
    const { headerId, signFlag, signComment, account } = req.body
    let sql =
      " begin xx_general_asset_pkg.xx_general_asset_report_main('" +
      headerId +
      "','" +
      signFlag +
      "','" +
      signComment +
      "','" +
      account +
      "');end; "
    callbackFn(res, oracle.connChaos, { sql })
  })

  router.post("/getApproveList", (req, res) => {
    const { account } = req.body
    let sql = " select rownum seq,a.org_code,a.check_month,c.chinese_name,a.flow_phase,a.header_id "
    sql +=
      " from xx_general_asset_report a,xx_general_asset_report_flow b,user_info_n c "
    sql += " where a.header_id = b.header_id "
    sql += " and b.action_klm = c.account "
    sql += " and a.current_seq = b.current_seq "
    sql += " and a.flow_phase <> '結案' "
    sql += " and b.action_klm = '" + account + "' "
    callbackFn(res, oracle.connChaos, { sql })
  })

  router.post("/getReportSignFlow", (req, res) => {
    const { headerId } = req.body
    let sql = " select b.dept,b.chinese_name "
    sql += " ,to_char(a.updated_date,'yyyy/mm/dd hh24:mi:ss') updated_date "
    sql +=
      " ,decode(a.sign_flag,'Y','同意','N','退回','') sign_flag,a.sign_comment,a.flow_phase "
    sql += " from xx_general_asset_report_flow a,user_info_n b "
    sql += " where a.action_klm = b.account "
    sql += " and a.header_id = '" + headerId + "' "
    sql += " order by a.current_seq "
    callbackFn(res, oracle.connChaos, { sql })
  })

  return router
}
